package com.lg;

import java.sql.*;
import java.time.LocalDate;
import java.util.Random;

public class JDBCWork {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/jdbc_demo";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "0000000Lg";

    public static void main(String[] args) {
        try {
            // 注册驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");

            // CRUD 练习
            try (Connection connection = getConnection()) {
//                新增
//                createTeacher(connection,  "丁玺润", "JavaWeb", java.sql.Date.valueOf(LocalDate.of(1990, 1, 1)));
//                查询单个
//                readTeacher(connection, 2);
//                查询所有
//                readTeacher(connection);
//                改
//                updateTeacher(connection, 2, "丁玺润真帅", "JavaWeb", java.sql.Date.valueOf(LocalDate.of(1991, 2, 2)));
//                删除单个
//                deleteTeacher(connection, 1);
                // 删除所有
//                deleteTeacher(connection);

                // 批量插入练习
//                batchInsertTeachers(connection);

                // 可滚动结果集练习
                scrollableResultSet(connection);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
    }

    // 创建教师
    public static void createTeacher(Connection connection, String name, String course, java.sql.Date birthday) throws SQLException {
        String sql = "INSERT INTO teacher (name, course, birthday) VALUES (?,?,?)";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, name);
            statement.setString(2, course);
            statement.setDate(3, birthday);
            statement.executeUpdate();
            System.out.println("添加教师成功");
        }
    }

    // 读取教师
    // 读取特定教师信息
    public static void readTeacher(Connection connection, int id) throws SQLException {
        String sql = "SELECT * FROM teacher WHERE id =?";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, id);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    System.out.println("ID: " + resultSet.getInt("id"));
                    System.out.println("姓名: " + resultSet.getString("name"));
                    System.out.println("课程: " + resultSet.getString("course"));
                    System.out.println("生日: " + resultSet.getDate("birthday"));
                } else {
                    System.out.println("未找到ID为 " + id + " 的教师信息");
                }
            }
        }
    }

    // 读取所有教师信息
    public static void readTeacher(Connection connection) throws SQLException {
        String sql = "SELECT * FROM teacher";
        try (PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("id"));
                System.out.println("姓名: " + resultSet.getString("name"));
                System.out.println("课程: " + resultSet.getString("course"));
                System.out.println("生日: " + resultSet.getDate("birthday"));
                System.out.println("-----------");
            }
        }
    }

    // 更新教师
    public static void updateTeacher(Connection connection, int id, String name, String course, java.sql.Date birthday) throws SQLException {
        String sql = "UPDATE teacher SET name =?, course =?, birthday =? WHERE id =?";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, name);
            statement.setString(2, course);
            statement.setDate(3, birthday);
            statement.setInt(4, id);
            statement.executeUpdate();
            System.out.println("更新教师成功");
        }
    }

    // 删除教师
// 删除单个教师
    public static void deleteTeacher(Connection connection, int id) throws SQLException {
        String sql = "DELETE FROM teacher WHERE id =?";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, id);
            int rowsAffected = statement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("删除教师成功，ID：" + id);
            } else {
                System.out.println("未找到ID为 " + id + " 的教师信息");
            }
        } catch (SQLException e) {
            System.out.println("删除教师出错：" + e.getMessage());
        }
    }

    // 删除所有教师
    public static void deleteTeacher(Connection connection) throws SQLException {
        String sql = "DELETE FROM teacher";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            int rowsAffected = statement.executeUpdate();
            System.out.println("删除所有教师成功，共删除记录数：" + rowsAffected);
        } catch (SQLException e) {
            System.out.println("删除所有教师出错：" + e.getMessage());
        }
    }

    // 批量插入教师
    public static void batchInsertTeachers(Connection connection) throws SQLException {
        String sql = "INSERT INTO teacher ( name, course, birthday) VALUES (?,?,?)";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            connection.setAutoCommit(false);
            Random random = new Random();
            for (int i = 1; i <= 500; i++) {
                statement.setString(1, "Teacher" + i);
                statement.setString(2, "Course" + random.nextInt(10));
                statement.setDate(3, java.sql.Date.valueOf(LocalDate.of(1980, 1, 1).plusDays(random.nextInt(10000))));
                statement.addBatch();

                if (i % 100 == 0) {
                    statement.executeBatch();
                    connection.commit();
                }
            }
            statement.executeBatch();
            connection.commit();
            System.out.println("批量插入完成");
        } finally {
            connection.setAutoCommit(true);
        }
    }

    // 可滚动结果集练习
    public static void scrollableResultSet(Connection connection) throws SQLException {
        String sql = "SELECT * FROM teacher";
        try (PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
             ResultSet resultSet = statement.executeQuery()) {

            if (resultSet.last() && resultSet.previous()) { // 定位到倒数第二条数据
                System.out.println("倒数第二条数据:");
                System.out.println("ID: " + resultSet.getInt("id"));
                System.out.println("Name: " + resultSet.getString("name"));
                System.out.println("Course: " + resultSet.getString("course"));
                System.out.println("Birthday: " + resultSet.getDate("birthday"));
            }
        }
    }
}